-- SEQUENCES --
CREATE SEQUENCE DAM.DAM_DIMENSIONS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_DIMENSION_FIELDS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_FUNCTIONS_ID MINVALUE 0 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 0 NOCACHE NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_FUNCTION_FIELDS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM_FUNCTION_REF_ID INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_FIELD_ID MINVALUE 0 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 72 NOCACHE NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_FLOW_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 261 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_LOGS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_OPSETS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
CREATE SEQUENCE DAM.DAM_OPSET_FIELDS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;

  -- TABLES --
  CREATE TABLE DAM.DAM_LOGS
    (
      LOG_ID            NUMBER(38,0),
      PART_OF_FLOW      NUMBER(38,0),
      AFFECTED_FUNCTION NUMBER(38,0),
      STARTED_ON DATE,
      ENDED_ON DATE,
      ACTION       VARCHAR2(64),
      REC_RETURNED NUMBER(38,0),
      ERR_CODE     NUMBER(38,0),
      CONSTRAINT DAM_LOGS_DAM_FLOWS_FK1 FOREIGN KEY (LOG_ID) REFERENCES DAM_FLOWS (FLOW_ID) ON DELETE SET NULL ENABLE,
      CONSTRAINT DAM_LOGS_DAM_FUNCTIONS_FK1 FOREIGN KEY (LOG_ID) REFERENCES DAM_FUNCTIONS (FUNCTION_ID) ON DELETE SET NULL ENABLE
    );
    
  CREATE TABLE DAM.DAM_NAMESPACES
    (
      NAMESPACE_ID        NUMBER NOT NULL ENABLE,
      PARENT_NAMESPACE_FK NUMBER,
      DESCRIPTION         VARCHAR2(64) NOT NULL ENABLE,
      ANNOTATION          VARCHAR2(4000),
      CONSTRAINT DAM_NAMESPACES_PK PRIMARY KEY (NAMESPACE_ID),
      CONSTRAINT DAM_NAMESPACES_U01 UNIQUE (PARENT_NAMESPACE_FK, DESCRIPTION),
      CONSTRAINT DAM_NAMESPACES_R01 FOREIGN KEY (PARENT_NAMESPACE_FK) REFERENCES DAM.DAM_NAMESPACES (NAMESPACE_ID) ON
    DELETE CASCADE ENABLE
    );
  CREATE TABLE DAM.DAM_DIMENSIONS
    (
      DIMENSION_ID NUMBER(38,0),
      NAME         VARCHAR2(64) CONSTRAINT DAM_DIMENSIONS_C01 NOT NULL ENABLE,
      TABLE_NAME   VARCHAR2(30) CONSTRAINT DAM_DIMENSIONS_C02 NOT NULL ENABLE,
      DESCRIPTION  VARCHAR2(4000),
      MODIFIED_ON DATE,
      CREATED_ON DATE,
      CONSTRAINT DAM_DIMENSIONS_PK PRIMARY KEY (DIMENSION_ID),
      CONSTRAINT DAM_DIMENSIONS_U01 UNIQUE (NAME)
    );
  CREATE TABLE DAM.DAM_DIMENSION_FIELDS
    (
      DIMENSION_FIELD_ID NUMBER(38,0),
      FIELD_NAME         VARCHAR2(64),
      DB_FIELD_NAME      VARCHAR2(30),
      DIMENSION_FK       NUMBER(38,0),
      SEQ                NUMBER(38,0),
      DESCRIPTION        VARCHAR2(4000),
      MODIFIED_ON DATE,
      CREATED_ON DATE,
      NUMBER_FORMAT VARCHAR2(16),
      COLUMN_WIDTH  NUMBER DEFAULT 64,
      FIELD_TYPE    NUMBER(1,0) DEFAULT 0,
      CONSTRAINT DAM_DIMENSION_FIELDS_C02 CHECK (DB_FIELD_NAME IS NOT NULL) ENABLE,
      CONSTRAINT DAM_DIMENSION_FIELDS_C01 CHECK (FIELD_NAME    IS NOT NULL) ENABLE,
      CONSTRAINT DAM_DIMENSION_FIELDS_PK PRIMARY KEY (DIMENSION_FIELD_ID),
      CONSTRAINT DAM_DIMENSION_FIELDS_U01 UNIQUE (DIMENSION_FK, FIELD_NAME),
      CONSTRAINT DAM_DIMENSION_FIELDS_R01 FOREIGN KEY (DIMENSION_FK) REFERENCES DAM.DAM_DIMENSIONS (DIMENSION_ID) ON
    DELETE CASCADE ENABLE
    );
    COMMENT ON COLUMN DAM_DIMENSION_FIELDS.SEQ IS 'Fieldorder';
    COMMENT ON COLUMN DAM_DIMENSION_FIELDS.NUMBER_FORMAT IS 'Formatinformation for Number and Date fields';
    COMMENT ON COLUMN DAM_DIMENSION_FIELDS.FIELD_TYPE IS '0=ValueField; 1=ID;2=Description';
    
  CREATE TABLE DAM.DAM_FUNCTIONS
    (
      FUNCTION_ID      NUMBER(38,0),
      NAME             VARCHAR2(64) CONSTRAINT DAM_FUNCTIONS_C02 NOT NULL ENABLE,
      TABLE_NAME       VARCHAR2(30) CONSTRAINT DAM_FUNCTIONS_C03 NOT NULL ENABLE,
      WHERE_CONDITION  VARCHAR2(2048),
      HAVING_CONDITION VARCHAR2(2048),
      DESCRIPTION      VARCHAR2(4000),
      MODIFIED_ON DATE NOT NULL ENABLE,
      CREATED_ON DATE NOT NULL ENABLE,
      FUNC_TYPE    NUMBER(38,0) CONSTRAINT DAM_FUNCTIONS_C01 NOT NULL ENABLE,
      FLAT_TREE_OF NUMBER(38,0),
      LOCKED_BY    VARCHAR2(32) DEFAULT NULL,
      ACTION       NUMBER(38,0) DEFAULT 0 CONSTRAINT DAM_FUNCTIONS_C04 NOT NULL ENABLE,
      NAMESPACE_FK NUMBER,
      USE_GROUP_BY NUMBER DEFAULT 0,
      USE_DISTINCT NUMBER DEFAULT 0,
      CONSTRAINT DAM_FUNCTIONS_PK PRIMARY KEY (FUNCTION_ID),
      CONSTRAINT DAM_FUNCTIONS_U01 UNIQUE (NAME, NAMESPACE_FK),
      CONSTRAINT DAM_FUNCTIONS_R01 FOREIGN KEY (FLAT_TREE_OF) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ENABLE,
      CONSTRAINT DAM_FUNCTIONS_R02 FOREIGN KEY (NAMESPACE_FK) REFERENCES DAM.DAM_NAMESPACES (NAMESPACE_ID) ENABLE
    );
    COMMENT ON COLUMN DAM_FUNCTIONS.FUNC_TYPE IS '0=Internal;1=Input;2=Table;3=View';
    COMMENT ON COLUMN DAM_FUNCTIONS.ACTION IS '0=TruncateAndAdd;1=Add;2=Merge;3=Update';

  CREATE TABLE DAM.DAM_FUNCTION_FIELDS
    (
      FUNCTION_FIELD_ID NUMBER(38,0),
      FIELD_NAME        VARCHAR2(64) CONSTRAINT DAM_FUNCTION_FIELDS_C01 NOT NULL ENABLE,
      DB_FIELD_NAME     VARCHAR2(30) CONSTRAINT DAM_FUNCTION_FIELDS_C02 NOT NULL ENABLE,
      EXPRESSION        VARCHAR2(4000),
      DESCRIPTION       VARCHAR2(4000),
      SEQ               NUMBER(38,0),
      FUNCTION_FK       NUMBER(38,0) CONSTRAINT DAM_FUNCTION_FIELDS_C03 NOT NULL ENABLE,
      DIMENSION_FK      NUMBER(38,0),
      CREATED_ON DATE,
      MODIFIED_ON DATE,
      NUMBER_FORMAT VARCHAR2(16),
      COLUMN_WIDTH  NUMBER DEFAULT 64,
      PARENT_FIELD  NUMBER,
      DEL_COMPARE   NUMBER DEFAULT 0,
      FIELD_TYPE    NUMBER DEFAULT 0,
      CONSTRAINT DAM_FUNCTION_FIELDS_PK PRIMARY KEY (FUNCTION_FIELD_ID),
      CONSTRAINT DAM_FUNCTION_FIELDS_U01 UNIQUE (FUNCTION_FK, DB_FIELD_NAME),
      CONSTRAINT DAM_FUNCTION_FIELDS_U02 UNIQUE (FUNCTION_FK, FIELD_NAME),
      CONSTRAINT DAM_FUNCTION_FIELDS_R01 FOREIGN KEY (DIMENSION_FK) REFERENCES DAM.DAM_DIMENSIONS (DIMENSION_ID) ON
    DELETE
      SET NULL ENABLE,
      CONSTRAINT DAM_FUNCTION_FIELDS_R02 FOREIGN KEY (FUNCTION_FK) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ON
    DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FUNCTION_FIELDS_R03 FOREIGN KEY (PARENT_FIELD) REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID) ON
    DELETE SET NULL ENABLE
    );
    COMMENT ON COLUMN DAM_FUNCTION_FIELDS.FIELD_TYPE IS '0=Non-Key-Field;1= Field is part of th PK';

  CREATE TABLE DAM.DAM_FUNCTION_OPSETS
    (
      OPSET_ID          NUMBER,
      FUNCTION_FK       NUMBER CONSTRAINT DAM_FUNCTION_OPSETS_C01 NOT NULL ENABLE,
      OPSET             VARCHAR2(16) CONSTRAINT DAM_FUNCTION_OPSETS_C02 NOT NULL ENABLE,
      OPSET_FUNCTION_FK NUMBER CONSTRAINT DAM_FUNCTION_OPSETS_C03 NOT NULL ENABLE,
      CONSTRAINT DAM_FUNCTION_OPSETS_PK PRIMARY KEY (OPSET_ID),
      CONSTRAINT DAM_FUNCTION_OPSETS_R01 FOREIGN KEY (FUNCTION_FK) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ON
    DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FUNCTION_OPSETS_R02 FOREIGN KEY (OPSET_FUNCTION_FK) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ON
    DELETE CASCADE ENABLE
    );
    COMMENT ON COLUMN DAM_FUNCTION_OPSETS.OPSET IS 'can be ''UNION'';''UNION ALL'';''INTERSECT'';''MINUS''';

  CREATE TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
    (
      OPSET_FIELD_ID    NUMBER,
      FUNCTION_OPSET_FK NUMBER CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C01 NOT NULL ENABLE,
      FUNCTION_FIELD_FK NUMBER,
      SEQ               NUMBER CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C02 NOT NULL ENABLE,
      CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_PK PRIMARY KEY (OPSET_FIELD_ID),
      CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R01 FOREIGN KEY (FUNCTION_FIELD_FK) REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID) ON
    DELETE
      SET NULL ENABLE,
      CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R02 FOREIGN KEY (FUNCTION_OPSET_FK) REFERENCES DAM.DAM_FUNCTION_OPSETS (OPSET_ID) ON
    DELETE CASCADE ENABLE
    );
  CREATE TABLE DAM.DAM_FUNCTION_REF
    ( FUNC_REF_ID      NUMBER, 
      FUNCTION_FK      NUMBER,
      USES_FUNCTION_FK NUMBER,
      CREATED_ON DATE,
      MODIFIED_ON DATE,
      POS_X      NUMBER,
      POS_Y      NUMBER,
      ALIAS      VARCHAR2(64) NOT NULL ENABLE,
      ANNOTATION VARCHAR2(4000),
      CONSTRAINT DAM_FUNCTION_REF_PK PRIMARY KEY (FUNC_REF_ID),
      CONSTRAINT DAM_FUNCTION_REF_UK1 UNIQUE (FUNCTION_FK, USES_FUNCTION_FK, ALIAS),
      CONSTRAINT DAM_FUNCTION_REF_R01 FOREIGN KEY (FUNCTION_FK) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ON DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FUNCTION_REF_R02 FOREIGN KEY (USES_FUNCTION_FK) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ON DELETE CASCADE ENABLE
    );
   
  CREATE TABLE DAM.DAM_FUNCTION_JOINS
    (
      FUNCTION_FK       NUMBER CONSTRAINT DAM_FUNCTION_JOINS_C01 NOT NULL ENABLE,
      LEFT_FUNC_REF_FK  NUMBER CONSTRAINT DAM_FUNCTION_JOINS_C02 NOT NULL ENABLE,
      LEFT_FIELD_FK     NUMBER CONSTRAINT DAM_FUNCTION_JOINS_C03 NOT NULL ENABLE,
      RIGHT_FUNC_REF_FK NUMBER CONSTRAINT DAM_FUNCTION_JOINS_C04 NOT NULL ENABLE,
      RIGHT_FIELD_FK    NUMBER CONSTRAINT DAM_FUNCTION_JOINS_C05 NOT NULL ENABLE,
      JOIN_TYPE         NUMBER DEFAULT 0 CONSTRAINT DAM_FUNCTION_JOINS_C06 NOT NULL ENABLE,
      CONSTRAINT DAM_FUNCTION_JOINS_PK PRIMARY KEY (FUNCTION_FK, LEFT_FIELD_FK, RIGHT_FIELD_FK),
      CONSTRAINT DAM_FUNCTION_JOINS_R01 FOREIGN KEY (FUNCTION_FK) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ON DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FUNCTION_JOINS_R02 FOREIGN KEY (LEFT_FUNC_REF_FK) REFERENCES DAM.DAM_FUNCTION_REF (FUNC_REF_ID) ON DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FUNCTION_JOINS_R03 FOREIGN KEY (LEFT_FIELD_FK) REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID) ON DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FUNCTION_JOINS_R04 FOREIGN KEY (RIGHT_FUNC_REF_FK) REFERENCES DAM.DAM_FUNCTION_REF (FUNC_REF_ID) ON DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FUNCTION_JOINS_R05 FOREIGN KEY (RIGHT_FIELD_FK) REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID) ON DELETE CASCADE ENABLE
    );
    COMMENT ON COLUMN DAM_FUNCTION_JOINS.JOIN_TYPE IS '-1=LeftJoin;0=InnerJoin;1=RightJoin';

  CREATE TABLE DAM.DAM_FLOWS
    (
      FLOW_ID    NUMBER(38,0),
      FLOW_NAME  VARCHAR2(64) CONSTRAINT DAM_FLOWS_C01 NOT NULL ENABLE,
      ANNOTATION VARCHAR2(4000),
      CREATED_ON DATE,
      MODIFIED_ON DATE,
      PARENT_FLOW_FK NUMBER(38,0),
      CONSTRAINT DAM_FLOWS_PK PRIMARY KEY (FLOW_ID),
      CONSTRAINT DAM_FLOWS_U01 UNIQUE (FLOW_NAME, PARENT_FLOW_FK),
      CONSTRAINT DAM_FLOWS_R01 FOREIGN KEY (PARENT_FLOW_FK) REFERENCES DAM.DAM_FLOWS (FLOW_ID) ON
    DELETE CASCADE ENABLE
    );
  CREATE TABLE DAM.DAM_FLOW_FUNCTIONS
    (
      FLOW_FK     NUMBER(38,0),
      FUNCTION_FK NUMBER(38,0),
      IS_ACTIVE   NUMBER(38,0),
      CREATED_ON DATE,
      MODIFIED_ON DATE,
      CONSTRAINT DAM_FLOW_FUNCTIONS_PK PRIMARY KEY (FLOW_FK, FUNCTION_FK),
      CONSTRAINT DAM_FLOW_FUNCTIONS_R01 FOREIGN KEY (FLOW_FK) REFERENCES DAM.DAM_FLOWS (FLOW_ID) ON
    DELETE CASCADE ENABLE,
      CONSTRAINT DAM_FLOW_FUNCTIONS_R02 FOREIGN KEY (FUNCTION_FK) REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID) ON
    DELETE CASCADE ENABLE
    );
    
-- VIEWS --
CREATE OR REPLACE FORCE VIEW DAM.DAM_FUNCTION_REF_LVL (FUNCTION_ID, USES_FUNCTION_FK, LVL)
AS
  SELECT d.function_id, d.uses_function_fk, MAX (d.lvl) lvl
  FROM (SELECT f.function_id, fr.uses_function_fk, LEVEL lvl
        FROM dam_function_ref fr, dam_functions f
        WHERE f.function_id               = fr.function_fk
        CONNECT BY PRIOR fr.function_fk = fr.uses_function_fk
        UNION
        SELECT f.function_id, NULL, 0 lvl
        FROM dam_function_ref fr, dam_functions f
        WHERE f.function_id = fr.function_fk(+) AND fr.function_fk IS NULL) d
  GROUP BY d.function_id, d.uses_function_fk
  ORDER BY MAX (d.lvl), d.function_id;

-- INSERT TRIGGER --
CREATE OR REPLACE TRIGGER DAM.DAM_OPSET_INSERT_TRIGGER 
BEFORE INSERT ON DAM.DAM_FUNCTION_OPSETS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  tmpVar := 0;
  SELECT DAM_OPSETS_ID.NEXTVAL INTO tmpVar FROM dual;
  :NEW.OPSET_ID := tmpVar;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_OPSET_INSERT_TRIGGER;
ALTER TRIGGER DAM.DAM_OPSET_INSERT_TRIGGER ENABLE;
  
CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_FIELDS_INSERT_TRIGGER
BEFORE INSERT ON DAM.DAM_FUNCTION_FIELDS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE 
BEGIN 
  :NEW.db_field_name := UPPER(:NEW.db_field_name);
  :NEW.Created_On  := SYSDATE;
  :NEW.Modified_On := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_FUNC_FIELDS_INSERT_TRIGGER;
ALTER TRIGGER DAM.DAM_FUNC_FIELDS_INSERT_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_OPSET_FIELD_INSERT_TRIGGER 
BEFORE INSERT ON DAM.DAM_FUNCTION_OPSET_FIELDS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  tmpVar := 0;
  SELECT DAM_OPSET_FIELDS_ID.NEXTVAL INTO tmpVar FROM dual;
  :NEW.OPSET_FIELD_ID := tmpVar;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_OPSET_FIELD_INSERT_TRIGGER;
ALTER TRIGGER DAM.DAM_OPSET_FIELD_INSERT_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_DIM_FIELDS_INSERT_TRIGGER
BEFORE INSERT ON DAM.DAM_DIMENSION_FIELDS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  :NEW.db_field_name := UPPER(:NEW.db_field_name);
  :NEW.Created_On  := SYSDATE;
  :NEW.Modified_On := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END;
ALTER TRIGGER DAM.DAM_DIM_FIELDS_INSERT_TRIGGER ENABLE;
  
CREATE OR REPLACE TRIGGER DAM.DAM_DIM_INSERT_TRIGGER
BEFORE INSERT ON DAM.DAM_DIMENSIONS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  :NEW.table_name := UPPER(:NEW.table_name);
  :NEW.Created_On  := SYSDATE;
  :NEW.Modified_On := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END;
ALTER TRIGGER DAM.DAM_DIM_INSERT_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_LOGS_INSERT_TRIGGER
BEFORE INSERT ON DAM.DAM_LOGS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
BEGIN
  SELECT DAM_LOGS_ID.NEXTVAL INTO :NEW.LOG_ID FROM dual;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_LOGS_INSERT_TRIGGER;
ALTER TRIGGER DAM.DAM_LOGS_INSERT_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_INSERT_TRIGGER
BEFORE INSERT ON DAM.DAM_FUNCTIONS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  :NEW.table_name := UPPER(:NEW.table_name);
  :NEW.Created_On  := SYSDATE;
  :NEW.Modified_On := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_FUNC_INSERT_TRIGGER;
ALTER TRIGGER DAM.DAM_FUNC_INSERT_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_REF_INSERT_TRIGGER
BEFORE INSERT ON DAM.DAM_FUNCTION_REF REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  SELECT DAM_FUNCTION_REF_ID.NEXTVAL INTO tmpVar FROM dual;
  :NEW.Created_On  := SYSDATE;
  :NEW.Modified_On := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_FUNC_REF_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM.DAM_FLOW_FUNC_INSERT_TRIGGER 
BEFORE INSERT ON DAM.DAM_FLOW_FUNCTIONS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  :NEW.Created_On  := SYSDATE;
  :NEW.Modified_On := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END ;
ALTER TRIGGER DAM.DAM_FLOW_FUNC_INSERT_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_FLOW_INSERT_TRIGGER
BEFORE INSERT ON DAM.DAM_FLOWS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE tmpVar NUMBER;
BEGIN
  :NEW.Created_On  := SYSDATE;
  :NEW.Modified_On := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END;
ALTER TRIGGER DAM.DAM_FLOW_INSERT_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_DIM_UPDATE_TRIGGER
BEFORE UPDATE OF NAME, TABLE_NAME, DESCRIPTION 
ON DAM.DAM_DIMENSIONS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE 
BEGIN 
  :NEW.table_name := UPPER(:NEW.table_name);
  :NEW.Modified_On:= SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_DIM_UPDATE_TRIGGER;
ALTER TRIGGER DAM.DAM_DIM_UPDATE_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_FIELDS_UPDATE_TRIGGER 
BEFORE UPDATE OF FIELD_NAME, DB_FIELD_NAME, EXPRESSION, DESCRIPTION,
                 SEQ, DIMENSION_FK, PARENT_FIELD, DEL_COMPARE, FIELD_TYPE 
ON DAM.DAM_FUNCTION_FIELDS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE 
BEGIN
  SELECT SYSDATE INTO :NEW.Modified_On FROM dual;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END ;
ALTER TRIGGER DAM.DAM_FUNC_FIELDS_UPDATE_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_DIM_FIELDS_UPDATE_TRIGGER
BEFORE UPDATE OF FIELD_NAME, DB_FIELD_NAME, SEQ, DESCRIPTION 
ON DAM.DAM_DIMENSION_FIELDS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
BEGIN
  SELECT SYSDATE INTO :NEW.Modified_On FROM dual;
  :NEW.DB_FIELD_NAME := UPPER(:NEW.DB_FIELD_NAME);
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_DIM_FIELDS_UPDATE_TRIGGER;
ALTER TRIGGER DAM.DAM_DIM_FIELDS_UPDATE_TRIGGER ENABLE;

CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_UPDATE_TRIGGER
BEFORE UPDATE OF NAME, TABLE_NAME, WHERE_CONDITION,
                 HAVING_CONDITION, DESCRIPTION, ACTION, USE_GROUP_BY, USE_DISTINCT 
ON DAM.DAM_FUNCTIONS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
DECLARE 
BEGIN 
  :NEW.table_name := UPPER(:NEW.table_name);
  :NEW.Modified_On:= SYSDATE;
EXCEPTION
WHEN OTHERS THEN
  -- Consider logging the error and then re-raise
  RAISE;
END DAM_FUNC_UPDATE_TRIGGER;
ALTER TRIGGER DAM.DAM_FUNC_UPDATE_TRIGGER ENABLE;

CREATE OR REPLACE PROCEDURE DAM_DISABLE_CONSTRAINTS(Tbl_Name IN VARCHAR2) AS 
BEGIN
  FOR tbl IN (SELECT constraint_name, table_name FROM user_constraints 
              WHERE table_name=tbl_name AND constraint_type<>'P') LOOP
    EXECUTE IMMEDIATE 'alter table ' || tbl.table_name || ' disable constraint ' || tbl.constraint_name;
  END LOOP;
END DAM_DISABLE_CONSTRAINTS;

CREATE OR REPLACE PROCEDURE DAM_ENABLE_CONSTRAINTS(Tbl_Name IN VARCHAR2) AS 
BEGIN
  FOR tbl IN (SELECT constraint_name, table_name FROM user_constraints 
              WHERE  table_name=tbl_name AND constraint_type<>'P') LOOP
    EXECUTE IMMEDIATE 'alter table ' || tbl.table_name || ' enable constraint ' || tbl.constraint_name;
  END LOOP;
END DAM_ENABLE_CONSTRAINTS;